# -*- coding: utf-8 -*-
import pymysql

from ..config_util import mysql_config


class ListShopInfoMysqlConnectUtil(object):
    def __enter__(self):
        select_sql = "SELECT detail_url AS url, label AS category, region AS region FROM list_page;"
        # alter table shop_info add `lng` VARCHAR (100) NULL COMMENT '经度' after business_hours;
        # alter table shop_info add `lat` VARCHAR (100) NULL COMMENT '纬度' after lng;
        create_sql = """
        CREATE TABLE IF NOT EXISTS `dp`.`shop_info`  (
          `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
          `_rank` varchar(10)  NOT NULL COMMENT '排名',
          `shop_name` varchar(200)  NOT NULL COMMENT '店铺名称',
          `another_name` varchar(1000)  NULL DEFAULT NULL COMMENT '别名',
          `shop_id` varchar(200)  NOT NULL COMMENT '店铺ID',
          `shop_tags` varchar(300)  NULL DEFAULT NULL COMMENT '店铺标签',
          `star_level` varchar(100)  NULL DEFAULT NULL COMMENT '星级',
          `district` varchar(200)  NULL DEFAULT NULL COMMENT '商业区',
          `taste` varchar(10)  NULL DEFAULT NULL COMMENT '口味',
          `environment` varchar(10)  NULL DEFAULT NULL COMMENT '环境',
          `serve` varchar(10)  NULL DEFAULT NULL COMMENT '服务',
          `average_price` varchar(100)  NULL DEFAULT NULL COMMENT '人均消费',
          `region` varchar(100)  NOT NULL COMMENT '地区',
          `category` varchar(100)  NOT NULL COMMENT '类别',
          `restaurant_abstract` longtext  NULL COMMENT '餐厅简介',
          `comment_number` varchar(100)  NULL DEFAULT NULL COMMENT '评论数量',
          `detailed_address` varchar(100)  NULL DEFAULT NULL COMMENT '详细地址',
          `contact_way` varchar(100)  NULL DEFAULT NULL COMMENT '联系方式',
          `business_hours` varchar(100)  NULL DEFAULT NULL COMMENT '营业时间',
          `lng` VARCHAR (100) NULL COMMENT '经度',
          `lat` VARCHAR (100) NULL COMMENT '纬度',
          `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
          `update_time` timestamp(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0),
          PRIMARY KEY (`id`) USING BTREE,
          UNIQUE INDEX `shop_id`(`shop_id`) USING BTREE
        ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '店铺详细信息' ROW_FORMAT = Dynamic;
        """
        insert_sql = "INSERT INTO shop_info(_rank, shop_name, shop_id, shop_tags, district, taste, " \
                     "environment, serve, average_price, region, category) VALUES" \
                     "({_rank!r}, {shop_name!r}, {shop_id!r}, {shop_tags!r}, {district!r}, {taste!r}, " \
                     "{environment!r}, {serve!r}, {average_price!r}, {region!r}, {category!r});"

        self.connection = pymysql.connect(**mysql_config)
        self.cursor = self.connection.cursor()

        try:
            self.cursor.execute(create_sql)
            self.connection.commit()
        except Exception as e:
            self.connection.rollback()
            raise Exception(str(e))

        return self.connection, self.cursor, select_sql, insert_sql

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.cursor.close()
        self.connection.close()
